﻿using System;
using System.Data;
using System.Text;
using System.Data.OracleClient;
using Maticsoft.DBUtility;//����������
namespace GMS.DAL
{
    /// <summary>
    /// ���ݷ����SYSPARAMETERSORADALࡣ
    /// </summary>
    public class SYSPARAMETERSORADAL
    {
        public SYSPARAMETERSORADAL()
        { }
        #region  成员方法

        /// <summary>
        /// 获取最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperOra.GetMaxID("SYSPARAMETERSID", "TAB_SYSPARAMETERS");
        }

        /// <summary>
        /// 返回dropdownlist的不重复内容
        /// </summary>
        public DataSet GetPropertyName()
        {
            string str = "Select SYSPARAMETERSID,PROPERTYNAME from TAB_SYSPARAMETERS where FATHERTYPEID is null or FATHERTYPEID = 0";
            return DbHelperOra.Query(str);
        }

        /// <summary>
        /// 返回dropdownlist的不重复内容
        /// </summary>
        /// <returns></returns>
        public DataSet GetTypeName()
        {
            string str = "Select distinct(TYPENAME) from TAB_SYSPARAMETERS";
            return DbHelperOra.Query(str);
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        public bool Exists(int SYSPARAMETERSID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from TAB_SYSPARAMETERS");
            strSql.Append(" where SYSPARAMETERSID=:SYSPARAMETERSID ");
            OracleParameter[] parameters = {
					new OracleParameter(":SYSPARAMETERSID", OracleType.Number,4)
                                           };
            parameters[0].Value = SYSPARAMETERSID;

            return DbHelperOra.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 新增
        /// </summary>
        public void Add(GMS.Model.SYSPARAMETERS model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TAB_SYSPARAMETERS(");
            strSql.Append("PROPERTYVALUE,PROPERTYNAME,FATHERTYPEID)");
            strSql.Append(" values (");
            strSql.Append(":PROPERTYVALUE,:PROPERTYNAME,:FATHERTYPEID)");
            OracleParameter[] parameters = {
					new OracleParameter(":PROPERTYVALUE", OracleType.Number,22),
					new OracleParameter(":PROPERTYNAME", OracleType.VarChar,50),
					new OracleParameter(":FATHERTYPEID", OracleType.Number,22)};
            parameters[0].Value = model.PROPERTYVALUE;
            parameters[1].Value = model.PROPERTYNAME;
            parameters[2].Value = model.FATHERTYPEID;

            DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新
        /// </summary>
        public void Update(GMS.Model.SYSPARAMETERS model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TAB_SYSPARAMETERS set ");
            strSql.Append("PROPERTYNAME=:PROPERTYNAME,");
            strSql.Append("PROPERTYVALUE=:PROPERTYVALUE,");
            strSql.Append("FATHERTYPEID=:FATHERTYPEID");
            strSql.Append(" where SYSPARAMETERSID=:SYSPARAMETERSID ");
            OracleParameter[] parameters = {
					new OracleParameter(":SYSPARAMETERSID", OracleType.Number,22),
					new OracleParameter(":PROPERTYNAME", OracleType.VarChar,100),
					new OracleParameter(":PROPERTYVALUE", OracleType.Int32),
					new OracleParameter(":FATHERTYPEID", OracleType.Number,22)};
            parameters[0].Value = model.SYSPARAMETERSID;
            parameters[1].Value = model.PROPERTYNAME;
            parameters[2].Value = model.PROPERTYVALUE;
            parameters[3].Value = model.FATHERTYPEID;

            DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除
        /// </summary>
        public void Delete(int SYSPARAMETERSID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from TAB_SYSPARAMETERS ");
            strSql.Append(" where SYSPARAMETERSID=:SYSPARAMETERSID ");
            OracleParameter[] parameters = {
					new OracleParameter(":SYSPARAMETERSID", OracleType.Number,4)};
            parameters[0].Value = SYSPARAMETERSID;

            DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除
        /// </summary>
        public void DeleteMulti(string SYSPARAMETERSIDS)
        {
            DbHelperOra.ExecuteSql("delete from TAB_SYSPARAMETERS where SYSPARAMETERSID in (" + SYSPARAMETERSIDS + ") OR FATHERTYPEID IN (" + SYSPARAMETERSIDS + ") ");
        }

        /// <summary>
        /// 获取模型
        /// </summary>
        public GMS.Model.SYSPARAMETERS GetModel(int SYSPARAMETERSID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.SYSPARAMETERSID,b.PROPERTYNAME as TYPENAME,a.PROPERTYVALUE,a.PROPERTYNAME,a.FATHERTYPEID from TAB_SYSPARAMETERS a left join TAB_SYSPARAMETERS b on b.SYSPARAMETERSID=a.FATHERTYPEID");
            strSql.Append(" where a.SYSPARAMETERSID=:SYSPARAMETERSID ");
            OracleParameter[] parameters = {
					new OracleParameter(":SYSPARAMETERSID", OracleType.Number,4)};
            parameters[0].Value = SYSPARAMETERSID;

            GMS.Model.SYSPARAMETERS model = new GMS.Model.SYSPARAMETERS();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["SYSPARAMETERSID"].ToString() != "")
                {
                    model.SYSPARAMETERSID = int.Parse(ds.Tables[0].Rows[0]["SYSPARAMETERSID"].ToString());
                }
                model.TYPENAME = ds.Tables[0].Rows[0]["TYPENAME"].ToString();
                if (ds.Tables[0].Rows[0]["PROPERTYVALUE"].ToString() != "")
                {
                    model.PROPERTYVALUE = int.Parse(ds.Tables[0].Rows[0]["PROPERTYVALUE"].ToString());
                }
                model.PROPERTYNAME = ds.Tables[0].Rows[0]["PROPERTYNAME"].ToString();
                model.FATHERTYPEID = int.Parse(ds.Tables[0].Rows[0]["FATHERTYPEID"].ToString());
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 参数列表
        /// </summary>
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select SYSPARAMETERSID,PROPERTYVALUE,PROPERTYNAME,FATHERTYPEID ");
            strSql.Append(" FROM TAB_SYSPARAMETERS ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.DataTable(strSql.ToString());
        }

        public DataSet GetNewList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.SYSPARAMETERSID,a.FATHERTYPEID,b.PROPERTYNAME as TYPENAME,a.PROPERTYNAME,a.PROPERTYVALUE");
            strSql.Append(" FROM TAB_SYSPARAMETERS a left join TAB_SYSPARAMETERS b on a.FATHERTYPEID = b.SYSPARAMETERSID");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere + " order by  b.PROPERTYNAME,PROPERTYVALUE");
            }
            return DbHelperOra.Query(strSql.ToString());
        }
        public DataSet GetNewList1(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.SYSPARAMETERSID,b.SYSPARAMETERSID as FATHERTYPEID,b.PROPERTYNAME as TYPENAME,a.PROPERTYNAME,a.PROPERTYVALUE");
            strSql.Append(" FROM TAB_SYSPARAMETERS a,TAB_SYSPARAMETERS b ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where a.FATHERTYPEID = b.SYSPARAMETERSID and b.PROPERTYNAME =" + "'" + strWhere + "'");
            }
            return DbHelperOra.Query(strSql.ToString());
        }
        public DataSet GetNewListForDetails(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.SYSPARAMETERSID,b.SYSPARAMETERSID as FATHERTYPEID,b.PROPERTYNAME as TYPENAME,a.PROPERTYNAME,a.PROPERTYVALUE ");
            strSql.Append(" FROM TAB_SYSPARAMETERS a,TAB_SYSPARAMETERS b ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where a.FATHERTYPEID = b.SYSPARAMETERSID and a.SYSPARAMETERSID =" + "'" + strWhere + "'");
            }
            return DbHelperOra.Query(strSql.ToString());
        }

        #endregion  

        /// <summary>
        /// 取最大值作为PropertyValue
        /// </summary>
        /// <param name="fatherid"></param>
        /// <returns></returns>
        public int GetNewPropertyValue(int fatherid)
        {
            return Convert.ToInt32(DbHelperOra.GetSingle("SELECT DECODE(MAX(PROPERTYVALUE),NULL,0,MAX(PROPERTYVALUE))+1 FROM TAB_SYSPARAMETERS WHERE FATHERTYPEID='" + fatherid + "'"));
        }

        /// <summary>
        /// 同级是否已存在同名参数
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="fatherid"></param>
        /// <returns></returns>
        public bool ExistsSamePropertyNameForAdd(string propertyName,int fatherid)
        {
            return DbHelperOra.Exists("SELECT COUNT(1) FROM TAB_SYSPARAMETERS WHERE FATHERTYPEID='" + fatherid + "' AND PROPERTYNAME='" + propertyName + "'");
        }
        /// <summary>
        /// 同级是否已存在同名参数
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="fatherid"></param>
        /// <returns></returns>
        public bool ExistsSamePropertyValueForAdd(string propertyValue, int fatherid)
        {
            return DbHelperOra.Exists("SELECT COUNT(1) FROM TAB_SYSPARAMETERS WHERE FATHERTYPEID='" + fatherid + "' AND PROPERTYVALUE='" + propertyValue + "'");
        }
        /// <summary>
        /// 同级是否已存在同名参数
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="fatherid"></param>
        /// <returns></returns>
        public bool ExistsSamePropertyNameForUpdate(string propertyName, int fatherid,int id)
        {
            return DbHelperOra.Exists("SELECT COUNT(1) FROM TAB_SYSPARAMETERS WHERE FATHERTYPEID='" + fatherid + "' AND PROPERTYNAME='" + propertyName + "' AND SYSPARAMETERSID!='"+id+"'");
        }
        /// <summary>
        /// 同级是否已存在同值参数
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="fatherid"></param>
        /// <returns></returns>
        public bool ExistsSamePropertyValueForUpdate(string propertyValue, int fatherid, int id)
        {
            return DbHelperOra.Exists("SELECT COUNT(1) FROM TAB_SYSPARAMETERS WHERE FATHERTYPEID='" + fatherid + "' AND PROPERTYVALUE='" + propertyValue + "' AND SYSPARAMETERSID!='" + id + "'");
        }

        public string GetReferences(string parameterIds)
        {
            DataTable dt = DbHelperOra.DataTable("SELECT C.COLCODE,T.TABCODE,T.TABNAME,C.COLNAME,C.FOREIGNFATHERCODE FROM TAB_TABCOLUMNS C,TAB_TABINFO T WHERE C.TABINFOID=T.AUTOID AND UPPER(C.FOREIGNTAB)='TAB_SYSPARAMETERS' AND C.FOREIGNFATHERCODE IN (" + parameterIds + ")");
            if (dt.Rows.Count == 0)
                return "";
            StringBuilder rtnStr = new StringBuilder();
            rtnStr.Append("\\n");
            rtnStr.Append("表|列|引用参数");
            rtnStr.Append("\\n");
            foreach (DataRow row in dt.Rows)
            {
                DataTable dttemp = DbHelperOra.DataTable("SELECT distinct P.PROPERTYNAME FROM " + row["TABCODE"].ToString() + " T,TAB_SYSPARAMETERS P WHERE T." + row["COLCODE"].ToString() + "=TO_CHAR(P.PROPERTYVALUE) AND P.FATHERTYPEID = '" + row["FOREIGNFATHERCODE"].ToString() + "' AND P.SYSPARAMETERSID IN(" + parameterIds + ")");
                if(dttemp.Rows.Count>0)
                {
                    rtnStr.Append(row["TABNAME"].ToString() + "|" + row["COLNAME"].ToString() + "|");
                    foreach (DataRow tprow in dttemp.Rows)
                    {
                        rtnStr.Append(tprow["PROPERTYNAME"].ToString()+",");
                    }
                    rtnStr.Length--;
                    rtnStr.Append("\\n");
                }
            }
            rtnStr.Append("删除前请先删除引用数据！");
            return rtnStr.ToString();

        }
    }
}